using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class Room
    {
        private string number;
        private RoomType roomType;

        public string Number
        {
            get { return number; }
        }
        private int id;

        public int Id
        {
            get
            {
                return id;
            }

        }

        public int Capacity
        {
            get
            {
                return roomType.Capacity;
            }

        }

        public string RoomType
        {
            get
            {
                return roomType.Name;
            }

        }

        public decimal RoomPrise
        {
            get
            {
                return roomType.Prise;
            }

        }

        private Room(string number, RoomType type)
        {
            this.number = number;
            this.roomType = type;
        }



        public static Room[] Get()
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            //string str = ConfigurationManager.ConnectionStrings["Connection"].ToString();
            string commandString =
                "select r.Number,t.Name,t.Capacity,t.Prise From Rooms r join RoomType t on r.RoomTypeID=t.RoomTypeID";
            SqlConnection conn = new SqlConnection(str);
            SqlCommand commandSelect = new SqlCommand(commandString);
            commandSelect.Connection = conn;
            conn.Open();
            try
            {

                SqlDataReader reader =
                    commandSelect.ExecuteReader();
                List<Room> result = new List<Room>();
                using (reader)
                {
                    while (reader.Read())
                    {
                        RoomType type = new RoomType();
                        type.Name = (string)reader["Name"];
                        type.Prise = (decimal)reader["Prise"];
                        type.Capacity = (int)reader["Capacity"];
                        Room r = new Room((string)reader["Number"], type);
                        result.Add(r);
                    }
                }
                return result.ToArray();
            }
            finally { conn.Close(); }

        }

        public static void InsertRoom(string number, RoomType roomType)
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            if (!Exist(number))
            {
                string commandString =
                    "declare @id int set @id=(select RoomtypeID from RoomType r where [Name]=@Name) Insert into Rooms(Number,RoomTypeID) values(@Number,@id)";

                SqlConnection conn = new SqlConnection(str);
                conn.Open();
                try
                {
                    SqlParameter rType = new SqlParameter("@Name", SqlDbType.NChar);
                    SqlParameter num = new SqlParameter("@Number", SqlDbType.NChar);
                    rType.Value = roomType.Name;
                    num.Value = number;

                    SqlCommand commandInsert = new SqlCommand(commandString);
                    commandInsert.Connection = conn;
                    commandInsert.Parameters.Add(rType);
                    commandInsert.Parameters.Add(num);
                    commandInsert.ExecuteNonQuery();
                }
                finally { conn.Close(); }
            }
        }

        private static bool Exist(string number)
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            string check = "Select * from Rooms where Number=@number";
            SqlConnection conn = new SqlConnection(str);
            SqlCommand commandSelect = new SqlCommand(check);
            SqlParameter num = new SqlParameter("@Number", SqlDbType.NChar);
            num.Value = number;
            commandSelect.Parameters.Add(num);
            commandSelect.Connection = conn;
            conn.Open();
            try
            {

                SqlDataReader reader =
                    commandSelect.ExecuteReader();
                List<Room> result = new List<Room>();
                using (reader)
                {
                    while (reader.Read())
                    {
                        RoomType type = new RoomType();
                        Room r = new Room((string)reader["Number"], type);
                        result.Add(r);
                    }
                }
                return result.Count != 0;
            }
            finally { conn.Close(); }

        }

        public static void InsertRoom(string number, string roomTypeName)
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            string commandString =
                "declare @id int set @id=(select RoomtypeID from RoomType r where [Name]=@Name) Insert into Rooms(Number,RoomTypeID) values(@Number,@id)";
            if (!Exist(number))
            {
                SqlConnection conn = new SqlConnection(str);
                conn.Open();
                try
                {
                    SqlParameter rType = new SqlParameter("@Name", SqlDbType.NChar);
                    SqlParameter num = new SqlParameter("@Number", SqlDbType.NChar);
                    rType.Value = roomTypeName;
                    num.Value = number;

                    SqlCommand commandInsert = new SqlCommand(commandString);
                    commandInsert.Connection = conn;
                    commandInsert.Parameters.Add(rType);
                    commandInsert.Parameters.Add(num);
                    commandInsert.ExecuteNonQuery();
                }
                finally { conn.Close(); }

            }
        }

        public static void ChangeRoom(string number, RoomType newRoomType)
        {
            string str = ConfigurationManager.ConnectionStrings[0].ToString();
            string commandString =
                "update Rooms SET RoomTypeID=(select RoomTypeID from RoomType where Name=@RoomType) from Rooms where Number=@RoomNumber";
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            try
            {

                SqlParameter rType = new SqlParameter("@RoomType", SqlDbType.Int);
                SqlParameter rNum = new SqlParameter("@RoomNumber", SqlDbType.NChar);
                rType.Value = newRoomType;
                rNum.Value = number;

                SqlCommand commandUpdate = new SqlCommand(commandString);
                commandUpdate.Parameters.Add(rType);
                commandUpdate.Parameters.Add(rNum);
                commandUpdate.ExecuteNonQuery();
            }
            finally { conn.Close(); }
        }

        public static Dictionary<string, int> GetFreeRoomByType(DateTime date)
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            //string str = ConfigurationManager.ConnectionStrings["Connection"].ToString();
            string commandString =
                "select Count(*) as [count], t.Name From Rooms r join RoomType t on r.RoomTypeID=t.RoomTypeID left outer join Reservation on @date between StartDate and EndDate  where r.RoomID not in (SELECT RoomID From Reservation) group by [Name]";
            SqlParameter pDate = new SqlParameter("@date", SqlDbType.DateTime);
            pDate.Value = date;

            SqlConnection conn = new SqlConnection(str);
            SqlCommand commandSelect = new SqlCommand(commandString);
            commandSelect.Parameters.Add(pDate);
            commandSelect.Connection = conn;
            conn.Open();
            try
            {

                SqlDataReader reader =
                    commandSelect.ExecuteReader();
                Dictionary<string, int> types = new Dictionary<string, int>();
                using (reader)
                {
                    while (reader.Read())
                    {
                        types.Add(reader["Name"].ToString(), (int)reader["count"]);
                    }
                }
                return types;
            }
            finally { conn.Close(); }

        }

        public static Room[] GetFree(string roomType)
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            //string str = ConfigurationManager.ConnectionStrings["Connection"].ToString();
            string commandString =
                "select r.Number,e.Name,e.Capacity,e.Prise From Rooms r join RoomType e on e.Name=@name where r.RoomTypeID=e.RoomTypeID and r.RoomID not in (SELECT RoomID From Reservation)";
            SqlConnection conn = new SqlConnection(str);
            SqlCommand commandSelect = new SqlCommand(commandString);
            SqlParameter name = new SqlParameter("@name", DbType.String);
            name.Value = roomType;
            commandSelect.Parameters.Add(name);
            commandSelect.Connection = conn;
            conn.Open();
            try
            {

                SqlDataReader reader =
                    commandSelect.ExecuteReader();
                List<Room> result = new List<Room>();
                using (reader)
                {
                    while (reader.Read())
                    {
                        RoomType type = new RoomType();
                        type.Name = (string)reader["Name"];
                        type.Prise = (decimal)reader["Prise"];
                        type.Capacity = (int)reader["Capacity"];
                        Room r = new Room((string)reader["Number"], type);
                        result.Add(r);
                    }
                }
                return result.ToArray();
            }
            finally { conn.Close(); }
        }

        public static Room[] GetFree()
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            //string str = ConfigurationManager.ConnectionStrings["Connection"].ToString();
            string commandString =
                "select r.Number,t.Name,t.Capacity,t.Prise From Rooms r join RoomType t on r.RoomTypeID=t.RoomTypeID where r.RoomID not in (SELECT RoomID From Reservation)";
            SqlConnection conn = new SqlConnection(str);
            SqlCommand commandSelect = new SqlCommand(commandString);
            commandSelect.Connection = conn;
            conn.Open();
            try
            {

                SqlDataReader reader =
                    commandSelect.ExecuteReader();
                List<Room> result = new List<Room>();
                using (reader)
                {
                    while (reader.Read())
                    {
                        RoomType type = new RoomType();
                        type.Name = (string)reader["Name"];
                        type.Prise = (decimal)reader["Prise"];
                        type.Capacity = (int)reader["Capacity"];
                        Room r = new Room((string)reader["Number"], type);
                        result.Add(r);
                    }
                }
                return result.ToArray();
            }
            finally { conn.Close(); }


        }
        public static Room GetByNumber(string number)
        {
            string str = "Data Source=(local)\\sqlexpress;Initial Catalog=Hotel System;Integrated Security=True";
            //string str = ConfigurationManager.ConnectionStrings[0].ToString();
            string commandString =
                "select r.RoomID, r.Number,t.Name,t.Capacity,t.Prise From Rooms r,RoomType t where r.RoomTypeID=t.RoomTypeID and Number=@number";

            SqlConnection conn = new SqlConnection(str);
            SqlCommand commandSelect = new SqlCommand(commandString);
            commandSelect.Connection = conn;
            conn.Open();
            try
            {
                List<Room> result = new List<Room>();


                SqlParameter num = new SqlParameter("@number", SqlDbType.NChar);
                num.Value = number;
                commandSelect.Parameters.Add(num);
                SqlDataReader reader = commandSelect.ExecuteReader();
                using (reader)
                {
                    while (reader.Read())
                    {
                        RoomType type = new RoomType();
                        type.Name = (string)reader["Name"];
                        type.Prise = (decimal)reader["Prise"];
                        type.Capacity = (int)reader["Capacity"];
                        Room r = new Room((string)reader["Number"], type);
                        r.id = (int)reader["RoomID"];
                        result.Add(r);
                    }
                }
                return result[0];
            }
            finally { conn.Close(); }

        }
    }
}
